﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

using Serilog;
using Serilog.Core;

using SqlSugar;
using SqlSugar.IOC;

namespace IENMService.DAO
{
    /// <summary>
    /// Sugar ORM父类, 封装一些基本的操作
    /// </summary>
    /// <author>旷丽文</author>
    public class SugarBase
    {
        //private static readonly ILogger logger;

        //static SugarBase()
        //{
        //    logger = Log.Logger;
        //}

        /// <summary>
        /// 获取ORM数据库连接对象(只操作数据库一次的使用, 否则会进行多次数据库连接和关闭)
        /// 默认超时时间为30秒
        /// 默认为MySql数据库
        /// 默认自动关闭数据库链接, 多次操作数据库请勿使用该属性, 可能会造成性能问题
        /// 要自定义请使用GetIntance()方法或者直接使用Exec方法, 传委托
        /// </summary>
        public static ISqlSugarClient DB
        {
            get
            {
                //默认返回his数据库
                return InitDB("his", SqlSugar.DbType.SqlServer, 30, true);
            }
        }

        /// <summary>
        /// 获得SqlSugarClient(使用该方法, 默认请手动释放资源, 如using(var db = SugarBase.GetIntance()){你的代码}, 如果把isAutoCloseConnection参数设置为true, 则无需手动释放, 会每次操作数据库释放一次, 可能会影响性能, 请自行判断使用)
        /// </summary>
        /// <param name="connstr">数据库标识</param>
        /// <param name="commandTimeOut">等待超时时间, 默认为30秒 (单位: 秒)</param>
        /// <param name="dbType">数据库类型, 默认为SQL Server</param>
        /// <param name="isAutoCloseConnection">是否自动关闭数据库连接, 默认不是, 如果设置为true, 则会在每次操作完数据库后, 即时关闭, 如果一个方法里面多次操作了数据库, 建议保持为false, 否则可能会引发性能问题</param>
        /// <returns></returns>
        /// <author>旷丽文</author>
        public static ISqlSugarClient GetIntance(string connstr = "his", SqlSugar.DbType dbType = SqlSugar.DbType.SqlServer, int commandTimeOut = 30, bool isAutoCloseConnection = false)
        {
            return InitDB(connstr, dbType, commandTimeOut, isAutoCloseConnection);
        }

        /// <summary>
        /// 初始化ORM连接对象, 一般无需调用, 除非要自己写很复杂的数据库逻辑
        /// </summary>
        /// <param name="connstr">连接字符串</param>
        /// <param name="commandTimeOut">等待超时时间, 默认为30秒 (单位: 秒)</param>
        /// <param name="dbType">数据库类型, 默认为SQL Server</param>
        /// <param name="isAutoCloseConnection">是否自动关闭数据库连接, 默认不是, 如果设置为true, 则会在每次操作完数据库后, 即时关闭, 如果一个方法里面多次操作了数据库, 建议保持为false, 否则可能会引发性能问题</param>
        /// <author>旷丽文</author>
        private static ISqlSugarClient InitDB(string connstr = "his", SqlSugar.DbType dbType = SqlSugar.DbType.Oracle, int commandTimeOut = 30, bool isAutoCloseConnection = false)
        {
            return DbScoped.SugarScope.GetConnection(connstr);

            //if (string.IsNullOrWhiteSpace(connstr) || connstr.ToLower() == "his")
            //{
            //    //HIS数据库
            //    connstr = HisDBConnectionString;
            //    dbType = SqlSugar.DbType.SqlServer;
            //}
            //else if (connstr.ToLower() == "lis")
            //{
            //    //检验数据库
            //    connstr = LisDBConnectionString;
            //    //检验数据库类型
            //    dbType = SqlSugar.DbType.Oracle;
            //}
            //else if (connstr.ToLower() == "cdr")
            //{
            //    //cdr数据库
            //    connstr = CdrDBConnectionString;
            //    //cdr数据库类型
            //    dbType = SqlSugar.DbType.Oracle;
            //}
            //else if (connstr.ToLower() == "portal")
            //{
            //    //门户数据库
            //    connstr = PortalDBConnectionString;
            //    //门户数据库类型
            //    dbType = SqlSugar.DbType.SqlServer;
            //}
            //else
            //{
            //    //其他默认连接HIS数据库
            //    connstr = HisDBConnectionString;
            //    dbType = SqlSugar.DbType.SqlServer;
            //}

            //var db = new SqlSugarClient(new ConnectionConfig()
            //{
            //    ConnectionString = connstr,
            //    DbType = dbType,
            //    InitKeyType = InitKeyType.Attribute,
            //    IsAutoCloseConnection = isAutoCloseConnection
            //});

            //db.Ado.CommandTimeOut = commandTimeOut;

            ////用来打印Sql方便你调式    
            //db.Aop.OnLogExecuting = (sql, pars) =>
            //{
            //    Log.Debug($"\r\n\r\nSQL脚本:\r\n\r\n{sql}\r\n\r\n绑定参数:\r\n\r\n{db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value))}");
            //};

            //return db;
        }

        /// <summary>
        /// 执行数据库操作
        /// </summary>
        /// <typeparam name="Result"></typeparam>
        /// <param name="func"></param>
        /// <param name="commandTimeOut"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static Result Exec<Result>(Func<ISqlSugarClient, Result> func, int commandTimeOut = 30, SqlSugar.DbType dbType = SqlSugar.DbType.Oracle)
        {
            if (func == null) throw new Exception("委托为null, 事务处理无意义");

            var db = InitDB("his", dbType, commandTimeOut);

            try
            {
                return func(db);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 获取最大值
        /// </summary>
        /// <param name="identity">种子表</param>
        /// <param name="tname">表名</param>
        /// <param name="count">种子数</param>
        /// <param name="dbname">种子数据库</param>
        /// <returns></returns>
        public static int GetMax(string identity, string tname, int count, string dbname = "portal")
        {
            var db = InitDB(dbname, SqlSugar.DbType.SqlServer, 30, true);

            var dqz = db.Ado.GetInt("exec BSOFT_PRO_GET_MAX @p1,@p2,@p3", new { p1 = identity, p2 = tname, p3 = count });

            //by huangly 2022-06-10
            return dqz - count + 1;
        }

        /// <summary>
        /// 获取LIS种子值
        /// </summary>
        /// <param name="identity">种子表</param>
        /// <param name="count">获取数量</param>
        /// <returns></returns>
        public static int GetLisMax(string identity, int count = 1)
        {
            var db = InitDB("lis", SqlSugar.DbType.Oracle, 30, true);

            try
            {
                //开启事务
                db.Ado.BeginTran();

                //累计种子值
                db.Ado.ExecuteCommand($"UPDATE GY_XHB SET DQZ = DQZ + {count} WHERE BM = '{identity}'");

                //获取当前种子值
                var result = db.Ado.GetInt($"SELECT DQZ FROM GY_XHB WHERE BM = '{identity}'");

                //返回当前值
                result = result - count + 1;

                //提交事务
                db.Ado.CommitTran();

                return result;
            }
            catch (Exception ex)
            {
                db.Ado.RollbackTran();
                Log.Error(ex, "获取lis系统种子表失败!");
                throw;
            }
        }

        /// <summary>
        /// 带事务处理的执行数据库操作
        /// </summary>
        /// <typeparam name="Result"></typeparam>
        /// <param name="func"></param>
        /// <param name="connStr">数据库标识</param>
        /// <param name="commandTimeOut"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static Result ExecTran<Result>(Func<ISqlSugarClient, Result> func, string connStr = "his", int commandTimeOut = 30, SqlSugar.DbType dbType = SqlSugar.DbType.Oracle)
        {
            if (func == null) throw new Exception("委托为null, 事务处理无意义");

            var db = InitDB(connStr, dbType, commandTimeOut);

            try
            {
                db.Ado.BeginTran(IsolationLevel.ReadCommitted);

                var result = func(db);

                db.Ado.CommitTran();

                return result;
            }
            catch (Exception)
            {
                db.Ado.RollbackTran();

                throw;
            }
        }
    }
}